package au.com.mason.propertyTracker.service;

import java.util.Collections;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Service;

import au.com.mason.propertyTracker.compare.ReportComparator;
import au.com.mason.propertyTracker.dao.ReportRowMapper;
import au.com.mason.propertyTracker.dto.Report;

@Service
public class ReportService extends JdbcDaoSupport {
	
	private ReportRowMapper reportRowMapper;
	
	@Autowired
	public ReportService(ReportRowMapper reportRowMapper) {
		this.reportRowMapper = reportRowMapper;
	}

	public List<Report> getResults() {
		String query = "select suburbid, monthRequired, monthNumber, yearRequired, numberBedrooms, sum(new_prop) as new_prop, sum(new_prop_w_price) as new_prop_w_price, sum(avg_price) as avg_prop, sum(sold_prop) as sold_prop, sum(avg_on_market) as avg_on_market, sum(new_rent) as new_rent, sum(avg_rent) as avg_rent, sum(sold_rent) as sold_rent, sum(avg_rent_on_market) as avg_rent_on_market from " +
							"(" +
								getForSaleDateListed() +
							"union " +
								getForSaleStatusDate() +
							"union " +
								getRentalDateListed() +
							"union " +
								getRentalStatusDate() +		
							") tmp " +
							"group by regionid, suburbid, monthRequired, monthNumber, numberBedrooms";
		
		List<Report> reports = (List<Report>) getJdbcTemplate().query(query, reportRowMapper);
		
		Collections.sort(reports, new ReportComparator());
		
		return reports;
	}
	
	private String getForSaleDateListed() {
		return "(select regionid, suburbid, monthName(dateListed) as monthRequired, month(dateListed) as monthNumber, year(dateListed) as yearRequired, numberBedrooms, count(*) as new_prop, count(price) as new_prop_w_price, avg(price) as avg_price, 0 as sold_prop, 0 as avg_on_market, 0 as new_rent, 0 as avg_rent, 0 as sold_rent, 0 as avg_rent_on_market from property p inner join suburb s on p.suburbid = s.id " + 
				"where monitor = true and rental = false " +
				"group by regionid, suburbid, monthName(dateListed), month(dateListed), year(dateListed), numberBedrooms) ";
	}
	
	private String getForSaleStatusDate() {
		return "(select regionid, suburbid, monthName(statusDate) as monthRequired, month(statusDate) as monthNumber, year(statusDate) as yearRequired, numberBedrooms, 0 as new_prop, 0 as new_prop_w_price, 0 as avg_price, count(*) as sold_prop, avg(datediff(statusDate, dateListed)) as avg_on_market, 0 as new_rent, 0 as avg_rent, 0 as sold_rent, 0 as avg_rent_on_market from property p inner join suburb s on p.suburbid = s.id " + 
					"where monitor = true and statusDate is not null and rental = false " +
					"group by regionid, suburbid, monthName(statusDate), month(statusDate), year(statusDate), numberBedrooms) ";
	}
	
	private String getRentalDateListed() {
		return "(select regionid, suburbid, monthName(dateListed) as monthRequired, month(dateListed) as monthNumber, year(dateListed) as yearRequired, numberBedrooms, 0 as new_prop, 0 as new_prop_w_price, 0 as avg_price, 0 as sold_prop, 0 as avg_on_market, count(*) as new_rent, avg(rent) as avg_rent, 0 as sold_rent, 0 as avg_rent_on_market from property p inner join suburb s on p.suburbid = s.id " + 
					"where monitor = true and rental = true " +
					"group by regionid, suburbid, monthName(dateListed), month(dateListed), year(dateListed), numberBedrooms) ";
	}
	
	private String getRentalStatusDate() {
		return "(select regionid, suburbid, monthName(statusDate) as monthRequired, month(statusDate) as monthNumber, year(statusDate) as yearRequired, numberBedrooms, 0 as new_prop, 0 as new_prop_w_price, 0 as avg_price, 0 as sold_prop, 0 as avg_on_market, 0 as new_rent, 0 as avg_rent, count(*) as sold_rent, avg(datediff(statusDate, dateListed)) as avg_rent_on_market from property p inner join suburb s on p.suburbid = s.id " + 
					"where monitor = true and statusDate is not null and rental = true " +
					"group by regionid, suburbid, monthName(statusDate), month(statusDate), year(statusDate), numberBedrooms) ";
	}
	
}
